package com.ruoyi.service.jpjs.rlexportdata.impl;

import com.ruoyi.mapper.jpjs.PublicMapper;
import com.ruoyi.mapper.jpjs.empchange.EmpChangeMapper;
import com.ruoyi.mapper.jpjs.emponjob.EmpOnJobMapper;
import com.ruoyi.mapper.jpjs.empquit.EmpQuitMapper;
import com.ruoyi.service.jpjs.rlexportdata.RlExportDataOneService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zeo
 * @explain XXX
 * @date 2020/6/11 10:03
 */
@Service
public class RlExportDataOneServerImpl implements RlExportDataOneService {

    @Autowired
    EmpChangeMapper empChangeMapper;

    @Autowired
    EmpOnJobMapper empOnJobMapper;

    @Autowired
    EmpQuitMapper empQuitMapper;

    @Autowired
    PublicMapper publicMapper;

    @Override
    public void exportEmpChange(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        //新增工作薄
        HSSFSheet sheet = wb.createSheet("人员异动分析");
        //新增一行
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        //头部样式
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 15));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("一、人员异动分析");row.createCell(1);
        row.createCell(2);row.createCell(3);row.createCell(4);
        row.createCell(5);row.createCell(6);row.createCell(7);
        row.createCell(8);row.createCell(9);row.createCell(10);
        row.createCell(11);row.createCell(12);row.createCell(13);
        row.createCell(14);row.createCell(15);
        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 14));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));

        Map map2 = empChangeMapper.findTotalNum(years, months,maps);

        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("月初人数");
        row.createCell(2).setCellValue("入职人数");
        row.createCell(3).setCellValue("本月离职(" + map2.get("monthTotalQuitNum").toString() + ")人");
        row.createCell(4);row.createCell(5);row.createCell(6);
        row.createCell(7);
        row.createCell(8).setCellValue("人月异动(" + map2.get("monthTotalChangeNum").toString() + ")人");
        row.createCell(9);row.createCell(10);row.createCell(11);
        row.createCell(12);row.createCell(13);row.createCell(14);
        row.createCell(15).setCellValue("月末人数");
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }
        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1);
        row.createCell(2);
        row.createCell(3).setCellValue("主动离职");
        row.createCell(4).setCellValue("被动离职");
        row.createCell(5).setCellValue("月总离职人数");
        row.createCell(6).setCellValue("主动离职率");
        row.createCell(7).setCellValue("总离职率");
        row.createCell(8).setCellValue("调入");
        row.createCell(9).setCellValue("调出");
        row.createCell(10).setCellValue("晋升");
        row.createCell(11).setCellValue("降职");
        row.createCell(12).setCellValue("调岗");
        row.createCell(13).setCellValue("转正");
        row.createCell(14).setCellValue("调薪");
        row.createCell(15).setCellValue("本月异动");

        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;

        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);


        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = empChangeMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("monthFirstNum").toString());
                row.createCell(2).setCellValue(map1.get("entryNum").toString());
                row.createCell(3).setCellValue(map1.get("activeQuitNum").toString());
                row.createCell(4).setCellValue(map1.get("passiveQuitNum").toString());
                row.createCell(5).setCellValue(map1.get("monthTotalQuitNum").toString());
                row.createCell(6).setCellValue(map1.get("activeQuitRate").toString());
                row.createCell(7).setCellValue(map1.get("totalQuitRate").toString());
                row.createCell(8).setCellValue(map1.get("transferInNum").toString());
                row.createCell(9).setCellValue(map1.get("callOutNum").toString());
                row.createCell(10).setCellValue(map1.get("promotionNum").toString());
                row.createCell(11).setCellValue(map1.get("demotionNum").toString());
                row.createCell(12).setCellValue(map1.get("postTransferNum").toString());
                row.createCell(13).setCellValue(map1.get("justNum").toString());
                row.createCell(14).setCellValue(map1.get("changeSalary").toString());
                row.createCell(15).setCellValue(map1.get("monthEndNum").toString());
            } else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map2.get("monthFirstNum").toString());
        row.createCell(2).setCellValue(map2.get("entryNum").toString());
        row.createCell(3).setCellValue(map2.get("activeQuitNum").toString());
        row.createCell(4).setCellValue(map2.get("passiveQuitNum").toString());
        row.createCell(5).setCellValue(map2.get("monthTotalQuitNum").toString());
        //主动离职率 主动离职人数/月初人数*100
        row.createCell(6).setCellValue(map2.get("activeQuitRate").toString());
        //总离职率 总离职率/月初人数*100
        row.createCell(7).setCellValue(map2.get("totalQuitRate").toString());
        row.createCell(8).setCellValue(map2.get("transferInNum").toString());
        row.createCell(9).setCellValue(map2.get("callOutNum").toString());
        row.createCell(10).setCellValue(map2.get("promotionNum").toString());
        row.createCell(11).setCellValue(map2.get("demotionNum").toString());
        row.createCell(12).setCellValue(map2.get("postTransferNum").toString());
        row.createCell(13).setCellValue(map2.get("justNum").toString());
        row.createCell(14).setCellValue(map2.get("changeSalary").toString());
        row.createCell(15).setCellValue(map2.get("monthEndNum").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }


        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 14 * 256);


    }

    @Override
    public void exportEmpOnJob(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("在职人员分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 39));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("二、在职人员分析（月末数据）");
        row.createCell(1);row.createCell(2);row.createCell(3);
        row.createCell(4);row.createCell(5);row.createCell(6);
        row.createCell(7);row.createCell(8);row.createCell(9);
        row.createCell(10);row.createCell(11);row.createCell(12);
        row.createCell(13);row.createCell(14);row.createCell(15);
        row.createCell(16);row.createCell(17);row.createCell(18);
        row.createCell(19);row.createCell(20);row.createCell(21);
        row.createCell(22);row.createCell(23);row.createCell(24);
        row.createCell(25);row.createCell(26);row.createCell(27);
        row.createCell(28);row.createCell(29);row.createCell(30);
        row.createCell(31);row.createCell(32);row.createCell(33);
        row.createCell(34);row.createCell(35);row.createCell(36);
        row.createCell(37);row.createCell(38);row.createCell(39);

        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 13));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 23));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 24, 31));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 32, 39));

        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("月末人数");
        row.createCell(2).setCellValue("高层管理");
        row.createCell(3).setCellValue("占比");
        row.createCell(4).setCellValue("中层管理");
        row.createCell(5).setCellValue("占比");
        row.createCell(6).setCellValue("基层管理");
        row.createCell(7).setCellValue("占比");
        row.createCell(8).setCellValue("工人");
        row.createCell(9).setCellValue("占比");
        row.createCell(10).setCellValue("后勤(不含安保)");
        row.createCell(11).setCellValue("占比");
        row.createCell(12).setCellValue("结构性别");
        row.createCell(13);
        row.createCell(14).setCellValue("工龄结构");
        row.createCell(15);row.createCell(16);row.createCell(17);
        row.createCell(18);row.createCell(19);row.createCell(20);
        row.createCell(21);row.createCell(22);row.createCell(23);
        row.createCell(24).setCellValue("年龄结构");
        row.createCell(25);row.createCell(26);row.createCell(27);
        row.createCell(28);row.createCell(29);row.createCell(30);
        row.createCell(31);
        row.createCell(32).setCellValue("学历结构");
        row.createCell(33);row.createCell(34);row.createCell(35);
        row.createCell(36);row.createCell(37);row.createCell(38);row.createCell(39);

        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);row.createCell(1);row.createCell(2);
        row.createCell(3);row.createCell(4);row.createCell(5);
        row.createCell(6);row.createCell(7);row.createCell(8);
        row.createCell(9);row.createCell(10);row.createCell(11);
        row.createCell(12).setCellValue("男");
        row.createCell(13).setCellValue("女");
        row.createCell(14).setCellValue("＜1年");
        row.createCell(15).setCellValue("占比");
        row.createCell(16).setCellValue("1年≤？＜3年");
        row.createCell(17).setCellValue("占比");
        row.createCell(18).setCellValue("3年≤？＜5年");
        row.createCell(19).setCellValue("占比");
        row.createCell(20).setCellValue("5年≤？＜10年");
        row.createCell(21).setCellValue("占比");
        row.createCell(22).setCellValue("≥10年");
        row.createCell(23).setCellValue("占比");
        row.createCell(24).setCellValue("＜30岁");
        row.createCell(25).setCellValue("占比");
        row.createCell(26).setCellValue("30≤？＜40岁");
        row.createCell(27).setCellValue("占比");
        row.createCell(28).setCellValue("40≤？＜50岁");
        row.createCell(29).setCellValue("占比");
        row.createCell(30).setCellValue("≥50岁");
        row.createCell(31).setCellValue("占比");
        row.createCell(32).setCellValue("高中及以下(含中专)");
        row.createCell(33).setCellValue("占比");
        row.createCell(34).setCellValue("大专");
        row.createCell(35).setCellValue("占比");
        row.createCell(36).setCellValue("本科");
        row.createCell(37).setCellValue("占比");
        row.createCell(38).setCellValue("本科以上");
        row.createCell(39).setCellValue("占比");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);


        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = empOnJobMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("monthEndNum").toString());
                row.createCell(2).setCellValue(map1.get("highNum").toString());
                row.createCell(3).setCellValue(map1.get("highProportion").toString());
                row.createCell(4).setCellValue(map1.get("middleNum").toString());
                row.createCell(5).setCellValue(map1.get("middleProportion").toString());
                row.createCell(6).setCellValue(map1.get("basicNum").toString());
                row.createCell(7).setCellValue(map1.get("basicProportion").toString());
                row.createCell(8).setCellValue(map1.get("workerNum").toString());
                row.createCell(9).setCellValue(map1.get("workerProportion").toString());
                row.createCell(10).setCellValue(map1.get("logisticsNum").toString());
                row.createCell(11).setCellValue(map1.get("logisticsProportion").toString());
                row.createCell(12).setCellValue(map1.get("maleNum").toString());
                row.createCell(13).setCellValue(map1.get("femaleNum").toString());
                row.createCell(14).setCellValue(map1.get("oneYearLowerNum").toString());
                row.createCell(15).setCellValue(map1.get("oneYearLowerProportion").toString());
                row.createCell(16).setCellValue(map1.get("oneThreeNum").toString());
                row.createCell(17).setCellValue(map1.get("oneThreeProportion").toString());
                row.createCell(18).setCellValue(map1.get("threeFiveNum").toString());
                row.createCell(19).setCellValue(map1.get("threeFiveProportion").toString());
                row.createCell(20).setCellValue(map1.get("fiveTenNum").toString());
                row.createCell(21).setCellValue(map1.get("fiveTenProportion").toString());
                row.createCell(22).setCellValue(map1.get("tenYearUpperNum").toString());
                row.createCell(23).setCellValue(map1.get("tenYearUpperProportion").toString());

                row.createCell(24).setCellValue(map1.get("twentyThirtyAgeNum").toString());
                row.createCell(25).setCellValue(map1.get("twentyThirtyAgeProportion").toString());
                row.createCell(26).setCellValue(map1.get("thirtyFortyAgeNum").toString());
                row.createCell(27).setCellValue(map1.get("thirtyFortyAgeProportion").toString());
                row.createCell(28).setCellValue(map1.get("fortyFiftyAgeNum").toString());
                row.createCell(29).setCellValue(map1.get("fortyFiftyAgeProportion").toString());
                row.createCell(30).setCellValue(map1.get("fiftyUpperAgeNum").toString());
                row.createCell(31).setCellValue(map1.get("fiftyUpperAgeProportion").toString());
                row.createCell(32).setCellValue(map1.get("highLowerSchoolNum").toString());
                row.createCell(33).setCellValue(map1.get("highLowerSchoolProportion").toString());
                row.createCell(34).setCellValue(map1.get("juniorCollegeSchoolNum").toString());
                row.createCell(35).setCellValue(map1.get("juniorCollegeSchoolProportion").toString());
                row.createCell(36).setCellValue(map1.get("undergraduateSchoolNum").toString());
                row.createCell(37).setCellValue(map1.get("undergraduateSchoolProportion").toString());
                row.createCell(38).setCellValue(map1.get("undergraduateUpperSchoolNum").toString());
                row.createCell(39).setCellValue(map1.get("undergraduateUpperSchoolProportion").toString());
            } else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
                row.createCell(16).setCellValue(0);
                row.createCell(17).setCellValue(0);
                row.createCell(18).setCellValue(0);
                row.createCell(19).setCellValue(0);
                row.createCell(20).setCellValue(0);
                row.createCell(21).setCellValue(0);
                row.createCell(22).setCellValue(0);
                row.createCell(23).setCellValue(0);
                row.createCell(24).setCellValue(0);
                row.createCell(25).setCellValue(0);
                row.createCell(26).setCellValue(0);
                row.createCell(27).setCellValue(0);
                row.createCell(28).setCellValue(0);
                row.createCell(29).setCellValue(0);
                row.createCell(30).setCellValue(0);
                row.createCell(31).setCellValue(0);
                row.createCell(32).setCellValue(0);
                row.createCell(33).setCellValue(0);
                row.createCell(34).setCellValue(0);
                row.createCell(35).setCellValue(0);
                row.createCell(36).setCellValue(0);
                row.createCell(37).setCellValue(0);
                row.createCell(38).setCellValue(0);
                row.createCell(39).setCellValue(0);

            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = empOnJobMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("monthEndNum").toString());
        row.createCell(2).setCellValue(map.get("highNum").toString());
        row.createCell(3).setCellValue(map.get("highProportion").toString());
        row.createCell(4).setCellValue(map.get("middleNum").toString());
        row.createCell(5).setCellValue(map.get("middleProportion").toString());
        row.createCell(6).setCellValue(map.get("basicNum").toString());
        row.createCell(7).setCellValue(map.get("basicProportion").toString());
        row.createCell(8).setCellValue(map.get("workerNum").toString());
        row.createCell(9).setCellValue(map.get("workerProportion").toString());
        row.createCell(10).setCellValue(map.get("logisticsNum").toString());
        row.createCell(11).setCellValue(map.get("logisticsProportion").toString());
        row.createCell(12).setCellValue(map.get("maleNum").toString());
        row.createCell(13).setCellValue(map.get("femaleNum").toString());
        row.createCell(14).setCellValue(map.get("oneYearLowerNum").toString());
        row.createCell(15).setCellValue(map.get("oneYearLowerProportion").toString());
        row.createCell(16).setCellValue(map.get("oneThreeNum").toString());
        row.createCell(17).setCellValue(map.get("oneThreeProportion").toString());
        row.createCell(18).setCellValue(map.get("threeFiveNum").toString());
        row.createCell(19).setCellValue(map.get("threeFiveProportion").toString());
        row.createCell(20).setCellValue(map.get("fiveTenNum").toString());
        row.createCell(21).setCellValue(map.get("fiveTenProportion").toString());
        row.createCell(22).setCellValue(map.get("tenYearUpperNum").toString());
        row.createCell(23).setCellValue(map.get("tenYearUpperProportion").toString());

        row.createCell(24).setCellValue(map.get("twentyThirtyAgeNum").toString());
        row.createCell(25).setCellValue(map.get("twentyThirtyAgeProportion").toString());
        row.createCell(26).setCellValue(map.get("thirtyFortyAgeNum").toString());
        row.createCell(27).setCellValue(map.get("thirtyFortyAgeProportion").toString());
        row.createCell(28).setCellValue(map.get("fortyFiftyAgeNum").toString());
        row.createCell(29).setCellValue(map.get("fortyFiftyAgeProportion").toString());
        row.createCell(30).setCellValue(map.get("fiftyUpperAgeNum").toString());
        row.createCell(31).setCellValue(map.get("fiftyUpperAgeProportion").toString());
        row.createCell(32).setCellValue(map.get("highLowerSchoolNum").toString());
        row.createCell(33).setCellValue(map.get("highLowerSchoolProportion").toString());
        row.createCell(34).setCellValue(map.get("juniorCollegeSchoolNum").toString());
        row.createCell(35).setCellValue(map.get("juniorCollegeSchoolProportion").toString());
        row.createCell(36).setCellValue(map.get("undergraduateSchoolNum").toString());
        row.createCell(37).setCellValue(map.get("undergraduateSchoolProportion").toString());
        row.createCell(38).setCellValue(map.get("undergraduateUpperSchoolNum").toString());
        row.createCell(39).setCellValue(map.get("undergraduateUpperSchoolProportion").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }



        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 14 * 256);
        sheet.setColumnWidth(7, 14 * 256);
        sheet.setColumnWidth(8, 14 * 256);
        sheet.setColumnWidth(9, 14 * 256);
        sheet.setColumnWidth(10, 16 * 256);
        sheet.setColumnWidth(16, 14 * 256);
        sheet.setColumnWidth(18, 14 * 256);
        sheet.setColumnWidth(20, 16 * 256);
        sheet.setColumnWidth(26, 14 * 256);
        sheet.setColumnWidth(28, 14 * 256);
        sheet.setColumnWidth(11, 14 * 256);
        sheet.setColumnWidth(22, 14 * 256);
        sheet.setColumnWidth(32, 20 * 256);
        sheet.setColumnWidth(38, 14 * 256);
    }

    @Override
    public void exportEmpQuit(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("离职人员分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 21));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("三、离职人员分析");
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);
        row.createCell(7);
        row.createCell(8);
        row.createCell(9);
        row.createCell(10);
        row.createCell(11);
        row.createCell(12);
        row.createCell(13);
        row.createCell(14);
        row.createCell(15);
        row.createCell(16);
        row.createCell(17);
        row.createCell(18);
        row.createCell(19);
        row.createCell(20);
        row.createCell(21);

        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 13));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 17));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 18, 21));
        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("本月离职总人数");
        row.createCell(2).setCellValue("高层管理");
        row.createCell(3).setCellValue("中层管理");
        row.createCell(4).setCellValue("基层管理");
        row.createCell(5).setCellValue("工人");
        row.createCell(6).setCellValue("后勤(不含保安)");
        row.createCell(7).setCellValue("性别结构");
        row.createCell(9).setCellValue("工龄结构");
        row.createCell(14).setCellValue("年龄结构");
        row.createCell(18).setCellValue("学历结构");
        row.createCell(19);
        row.createCell(20);
        row.createCell(21);

        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);

        row.createCell(7).setCellValue("男");
        row.createCell(8).setCellValue("女");

        row.createCell(9).setCellValue("＜1年");
        row.createCell(10).setCellValue("1年≤？＜3年");
        row.createCell(11).setCellValue("3年≤？＜5年");
        row.createCell(12).setCellValue("5年≤？＜10年");
        row.createCell(13).setCellValue("≥10年");

        row.createCell(14).setCellValue("＜30岁");
        row.createCell(15).setCellValue("30≤？＜40岁");
        row.createCell(16).setCellValue("40≤？＜50岁");
        row.createCell(17).setCellValue("≥50岁");

        row.createCell(18).setCellValue("高中及以下(含中专)");
        row.createCell(19).setCellValue("大专");
        row.createCell(20).setCellValue("本科");
        row.createCell(21).setCellValue("本科及以上");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }


        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);

        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = empQuitMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("monthTotalQuitNum").toString());
                row.createCell(2).setCellValue(map1.get("highNum").toString());
                row.createCell(3).setCellValue(map1.get("middleNum").toString());
                row.createCell(4).setCellValue(map1.get("basicNum").toString());
                row.createCell(5).setCellValue(map1.get("workerNum").toString());
                row.createCell(6).setCellValue(map1.get("logisticsNum").toString());

                row.createCell(7).setCellValue(map1.get("maleNum").toString());
                row.createCell(8).setCellValue(map1.get("femaleNum").toString());

                row.createCell(9).setCellValue(map1.get("oneYearLowerNum").toString());
                row.createCell(10).setCellValue(map1.get("oneThreeNum").toString());
                row.createCell(11).setCellValue(map1.get("threeFiveNum").toString());
                row.createCell(12).setCellValue(map1.get("fiveTenNum").toString());
                row.createCell(13).setCellValue(map1.get("tenYearUpperNum").toString());

                row.createCell(14).setCellValue(map1.get("twentyThirtyAgeNum").toString());
                row.createCell(15).setCellValue(map1.get("thirtyFortyAgeNum").toString());
                row.createCell(16).setCellValue(map1.get("fortyFiftyAgeNum").toString());
                row.createCell(17).setCellValue(map1.get("fiftyUpperAgeNum").toString());

                row.createCell(18).setCellValue(map1.get("highLowerSchoolNum").toString());
                row.createCell(19).setCellValue(map1.get("juniorCollegeSchoolNum").toString());
                row.createCell(20).setCellValue(map1.get("undergraduateSchoolNum").toString());
                row.createCell(21).setCellValue(map1.get("undergraduateUpperSchoolNum").toString());
            } else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
                row.createCell(16).setCellValue(0);
                row.createCell(17).setCellValue(0);
                row.createCell(18).setCellValue(0);
                row.createCell(19).setCellValue(0);
                row.createCell(20).setCellValue(0);
                row.createCell(21).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = empQuitMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("monthTotalQuitNum").toString());
        row.createCell(2).setCellValue(map.get("highNum").toString());
        row.createCell(3).setCellValue(map.get("middleNum").toString());
        row.createCell(4).setCellValue(map.get("basicNum").toString());
        row.createCell(5).setCellValue(map.get("workerNum").toString());
        row.createCell(6).setCellValue(map.get("logisticsNum").toString());
        row.createCell(7).setCellValue(map.get("maleNum").toString());
        row.createCell(8).setCellValue(map.get("femaleNum").toString());
        row.createCell(9).setCellValue(map.get("oneYearLowerNum").toString());
        row.createCell(10).setCellValue(map.get("oneThreeNum").toString());
        row.createCell(11).setCellValue(map.get("threeFiveNum").toString());
        row.createCell(12).setCellValue(map.get("fiveTenNum").toString());
        row.createCell(13).setCellValue(map.get("tenYearUpperNum").toString());

        row.createCell(14).setCellValue(map.get("twentyThirtyAgeNum").toString());
        row.createCell(15).setCellValue(map.get("thirtyFortyAgeNum").toString());
        row.createCell(16).setCellValue(map.get("fortyFiftyAgeNum").toString());
        row.createCell(17).setCellValue(map.get("fiftyUpperAgeNum").toString());
        row.createCell(18).setCellValue(map.get("highLowerSchoolNum").toString());
        row.createCell(19).setCellValue(map.get("juniorCollegeSchoolNum").toString());
        row.createCell(20).setCellValue(map.get("undergraduateSchoolNum").toString());
        row.createCell(21).setCellValue(map.get("undergraduateUpperSchoolNum").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }


        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 17 * 256);
        sheet.setColumnWidth(9, 14 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 14 * 256);
        sheet.setColumnWidth(12, 15 * 256);
        sheet.setColumnWidth(15, 14 * 256);
        sheet.setColumnWidth(16, 14 * 256);
        sheet.setColumnWidth(18, 20 * 256);
        sheet.setColumnWidth(21, 14 * 256);

    }


}
